package board;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

import com.oreilly.servlet.MultipartRequest;
import com.oreilly.servlet.multipart.DefaultFileRenamePolicy;


public class Board {

	int fileSize = 10 * 1024 * 1024;
	String uploadPath = "C:\\Users\\jhta\\workspace\\semi_webpage\\WebContent\\upload";
	String fileEncoding = "utf-8";

	// JDBC 연결 
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@192.168.10.108:1521:xe";
	String db_user = "hr";
	String db_pwd = "hr";	

	Connection con;
	BoardVo vo;
	
	// 페이지 분리 관련 필드 
	private int listSize = 10; // 한 페이지에 보여질 행 수
	private int blockSize = 2; // 한 번에 보여질 페이지 번호 수 (1, 2). 2개씩
	
	private int totSize = 0; // 검색된 데이터의 총 갯수 
	private int totPage = 0; // 전체 페이지 수
	private int totBlock = 0; // 전체 블럭수
	
	private int nowPage = 1; // 현재 페이지
	private int nowBlock = 1; // 현재 블럭
	
	private int startNo = 0; // 리스트 목록의 시작위치
	private int endNo = 0; // 리스트 목록의 마지막위치
	
	private int startPage = 0; // 한블럭에 표시할 시작 페이지 번호
	private int endPage = 0; // 한블럭에 표시할 마지막 페이지 번호

	public Board(){
		try {
			// 1. 드라이버 로딩 
			Class.forName(driver).newInstance();
			System.out.println("드라이버 로드됨");

			// 2. Connection 생성
			con = DriverManager.getConnection(url, db_user, db_pwd);
			System.out.println("커넥션 성공");
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}



	public BoardVo getVo() {
		return vo;
	}


	public void setVo(BoardVo vo) {
		this.vo = vo;
	}



	public String getUploadPath() {
		return uploadPath;
	}



	public void setUploadPath(String uploadPath) {
		this.uploadPath = uploadPath;
	}



	public Vector<BoardVo> list(){
		Vector<BoardVo> v = new Vector<>();
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try{
			
			// 전체 페이지 수 계산
			sql = "select count(serial) from board3 " +
					"where (mid like ? or subject like ? or content like ?) ";
			
			pstmt = con.prepareStatement(sql);
			
			pstmt.setString(1, "%"+vo.getFind()+"%");
			pstmt.setString(2, "%"+vo.getFind()+"%");
			pstmt.setString(3, "%"+vo.getFind()+"%");
		
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				setTotSize(rs.getInt(1));
				computePage();
			}
			
		
			sql = "select * from (" +
					"select rownum as no, a.* from (" +
					"select *from board3 " +
					"where (mid like ? or subject like ? or content like ?) " +
					"order by grp desc, deep asc) a )" +
					"where no between ? and ?";


		
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, "%"+vo.getFind()+"%");
			pstmt.setString(2, "%"+vo.getFind()+"%");
			pstmt.setString(3, "%"+vo.getFind()+"%");
			pstmt.setInt(4, startNo);
			pstmt.setInt(5, endNo);

			// 4. ResultSet 생성
			rs = pstmt.executeQuery();

			while(rs.next()){


				BoardVo vv = new BoardVo();

				vv.setSerial(rs.getInt("serial"));
				vv.setMid(rs.getString("mid"));
				vv.setSubject(rs.getString("subject"));
				vv.setMdate(rs.getDate("mdate"));
				vv.setHit(rs.getInt("hit"));
				vv.setGrp(rs.getInt("grp"));
				vv.setDeep(rs.getString("deep"));
				vv.setFlag(rs.getInt("flag"));

				v.add(vv);
			}

			// 다 썼으니깐, 닫장 빠2
			rs.close();
			pstmt.close();
			con.close();

		}catch (Exception ex) {
			ex.printStackTrace();
		}

		return v;

	}
	
	public void computePage(){
		totPage = (int)Math.ceil(totSize*1.0 / listSize);
		totBlock = (int)Math.ceil(totPage*1.0 / blockSize);
		
		nowBlock = (int)Math.ceil(nowPage*1.0 / blockSize);
		
		endPage = nowBlock * blockSize;
		startPage = endPage - blockSize + 1;
		
		if(totPage < endPage) endPage = totPage;
		
		endNo = nowPage * listSize; 
		startNo = endNo - listSize + 1;
		
		if(totSize < endNo) endNo = totSize;
	}

	public boolean insert(){

		boolean flag = false;
		
/*		File f = new File(uploadPath);
		if(!f.exists())	f.mkdir();*/
		
		String str = vo.getRequest().getServletContext().getRealPath("WebContent");
		str = str.replace("\\WebContent", "");
		
		String pro = str.substring(str.lastIndexOf("\\") + 1, str.length());
		
		str = str.substring(0, str.indexOf(".")) + pro + "\\WebContent\\upload"; 
		setUploadPath(str);

		try{
			MultipartRequest mr = new MultipartRequest(vo.getRequest(), uploadPath, 
					fileSize, fileEncoding, new DefaultFileRenamePolicy()); // 객체가 만들어지는 순간 파일 업로드 된다.

			String mid = mr.getParameter("mid");
			String pwd = mr.getParameter("pwd");
			String subject = mr.getParameter("subject");
			String content = mr.getParameter("content");

			//String orifile = mr.getOriginalFileName("filename");
			String sysfile = mr.getFilesystemName("filename");

			// DB 저장
			String sql = "insert all " +
					"into board3 (serial, mid, pwd, subject, content, grp, deep)" +
					"values (bd3_serial.nextval, ?, ?, ?, ?, bd3_serial.currval, bd3_serial.currval)";

			if (sysfile != null){
				sql += "into brdfiles2 (serial, fileid, filename) values " +
						"(brd2_serial.nextval, bd3_serial.currval, ?) ";
			}

			sql += "select *from dual";	

			PreparedStatement pstmt = con.prepareStatement(sql);

			pstmt.setString(1, mid);
			pstmt.setString(2, pwd);
			pstmt.setString(3, subject);
			pstmt.setString(4, content);

			if(sysfile != null)
				pstmt.setString(5, sysfile);

			if (pstmt.executeUpdate() > 0) flag = true;

			pstmt.close();
			con.close();


		}catch(Exception ex){
			ex.printStackTrace();
		}
		return flag;

	}

	public BoardVo view(){

		BoardVo m = null;
		String sql = null; 
		PreparedStatement pstmt = null;

		// 조회수 증가
		try{
			sql = "update board3 set hit = hit+1 where serial = " + vo.getSerial();
			pstmt = con.prepareStatement(sql);
			pstmt.executeUpdate();


			sql = "select b.*, f.* from " +
					"board3 b left outer join brdfiles2 f " +
					"on b.serial = f.fileid " +
					"where b.serial = ?";

			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, vo.getSerial());

			ResultSet rs = pstmt.executeQuery();

			if(rs.next()){
				m = new BoardVo();
				m.setSerial(rs.getInt("serial"));
				m.setMid(rs.getString("mid"));
				m.setSubject(rs.getString("subject"));
				m.setContent(rs.getString("content"));
				m.setMdate(rs.getDate("mdate"));
				m.setFilename(rs.getString("filename"));
				m.setHit(rs.getInt("hit"));
				m.setGrp(rs.getInt("grp"));
				m.setDeep(rs.getString("deep"));
			}

			rs.close();
			pstmt.close();
			con.close();

		}catch(Exception ex){
			ex.printStackTrace();
		}

		return m;
	}

	public boolean delete(){
		boolean flag = false;
		String sql = "delete from board3 where serial = ?";
		try{
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, vo.getSerial());

			if(pstmt.executeUpdate() > 0){
				sql = "select * from brdfiles2 where fileid = ?";
				pstmt = con.prepareStatement(sql);
				pstmt.setInt(1, vo.getSerial());
				
				ResultSet rs = pstmt.executeQuery();
				if(rs.next()){
					sql = "delete from brdfiles2 where fileid = " + vo.getSerial(); 
					pstmt = con.prepareStatement(sql);
					pstmt.executeUpdate();
				}
				
				File file = new File(uploadPath + "/" + vo.getFilename());
				if (file.exists()) file.delete(); // 파일이 존재하면 삭제
				else{} // 파일이 없을 경우, 보류
				
				flag = true;
				
				sql = "update board3 set flag = 1 where grp = " + vo.getSerial();
				pstmt = con.prepareStatement(sql);
				pstmt.executeUpdate();
				

				rs.close();
			}

			
			pstmt.close();
			con.close();

		}catch(Exception ex){
			ex.printStackTrace();
		}
		return flag;
	}
	
	public boolean reply(){ // grp, deep 조작이 가장 중요 ★
		boolean flag = false;
		
		String str = vo.getRequest().getServletContext().getRealPath("WebContent");
		str = str.replace("\\WebContent", "");
		
		String pro = str.substring(str.lastIndexOf("\\") + 1, str.length());
		
		str = str.substring(0, str.indexOf(".")) + pro + "\\WebContent\\upload"; 
		setUploadPath(str);
		
		try{
			MultipartRequest mr = new MultipartRequest(vo.getRequest(), uploadPath, 
					fileSize, fileEncoding, new DefaultFileRenamePolicy()); // 객체가 만들어지는 순간 파일 업로드 된다.

			String mid = mr.getParameter("mid");
			String pwd = mr.getParameter("pwd");
			String subject = mr.getParameter("subject");
			String content = mr.getParameter("content");

			//String orifile = mr.getOriginalFileName("filename");
			String sysfile = mr.getFilesystemName("filename");
			int grp = Integer.parseInt(mr.getParameter("grp"));
			String deep = mr.getParameter("deep");

			// DB 저장
			String sql = "insert all " +
					"into board3 (serial, mid, pwd, subject, content, grp, deep)" +
					"values (bd3_serial.nextval, ?, ?, ?, ?, ?, ? || bd3_serial.currval)";

			if (sysfile != null){
				sql += "into brdfiles2 (serial, fileid, filename) values " +
						"(brd2_serial.nextval, bd3_serial.currval, ?) ";
			}

			sql += "select *from dual";	

			PreparedStatement pstmt = con.prepareStatement(sql);

			pstmt.setString(1, mid);
			pstmt.setString(2, pwd);
			pstmt.setString(3, subject);
			pstmt.setString(4, content);
			
			pstmt.setInt(5, grp);
			pstmt.setString(6, deep + "-");

			if(sysfile != null)
				pstmt.setString(7, sysfile);

			if (pstmt.executeUpdate() > 0) flag = true;

			pstmt.close();
			con.close();


		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		return flag;
	}
	
	public boolean modify(){
		boolean flag = false;
		
		String str = vo.getRequest().getServletContext().getRealPath("WebContent");
		str = str.replace("\\WebContent", "");
		
		String pro = str.substring(str.lastIndexOf("\\") + 1, str.length());
		
		str = str.substring(0, str.indexOf(".")) + pro + "\\WebContent\\upload"; 
		setUploadPath(str);
		
		
		try{
			// 첨부 파일이 있는 경우, brdfile 테이블에 업데이트 
			// 없는 경우, brdfile 테이블에 삽입
			
			MultipartRequest mr = 
					new MultipartRequest(
							vo.getRequest(), 
							uploadPath,
							fileSize,
							fileEncoding,
							new DefaultFileRenamePolicy()); // 객체가 만들어지는 순간 파일 업로드 된다.
			
			int serial = Integer.parseInt(mr.getParameter("serial"));
			vo.setSerial(serial); // serial setting
			
			String pwd = mr.getParameter("pwd");
			String subject = mr.getParameter("subject");
			String content = mr.getParameter("content");
			String filename = mr.getParameter("filename"); // 기존 파일
			
			String fileDelFlag = mr.getParameter("fileDelFlag"); // 파일 삭제 여부 
			String sysfile = mr.getFilesystemName("newfilename"); // 새 파일
			
			PreparedStatement pstmt = null;
			String sql = null;
			
			// fileDelFlag == del 이면, 기존 첨부 파일에 삭제 버튼 누른 상태
			// 기존 첨부된 파일을 삭제 
			if(fileDelFlag.equals("del")){
				fileDel(filename, serial);
			}
			
			con.setAutoCommit(false); // 자동 커밋 방지
			
			sql = "update board3 set subject = ?, content = ?, mdate = sysdate " +
					"where serial = ? and pwd = ?";
			
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, subject);
			pstmt.setString(2, content);
			pstmt.setInt(3, serial);
			pstmt.setString(4, pwd);
		
			if (pstmt.executeUpdate() > 0) flag = true;
			
			if (sysfile != null && sysfile != ""){
				sql = "insert into brdfiles2 (serial, fileid, filename) " +
						"values (brd2_serial.nextval, ?, ?)";
				
				pstmt = con.prepareStatement(sql);
				pstmt.setInt(1, serial);
				pstmt.setString(2, sysfile);
				
				if (pstmt.executeUpdate() > 0){
					con.commit();
					flag = true;
				}
				else con.rollback();
			}
			
			pstmt.close();
			con.close();
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return flag;
	}
	
	public void fileDel(String filename, int serial){
		
		String sql = "delete from brdfiles2 where fileid = ? and filename = ?";
		try{
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, serial);
			pstmt.setString(2, filename);
			pstmt.executeUpdate();
			
			File file = new File(uploadPath + "/" + filename);
			if(file.exists()) file.delete(); // 해당 디렉토리에 파일이 존재하면, 삭제
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
	}



	public int getListSize() {
		return listSize;
	}



	public void setListSize(int listSize) {
		this.listSize = listSize;
	}



	public int getBlockSize() {
		return blockSize;
	}



	public void setBlockSize(int blockSize) {
		this.blockSize = blockSize;
	}



	public int getTotSize() {
		return totSize;
	}



	public void setTotSize(int totSize) {
		this.totSize = totSize;
	}



	public int getTotPage() {
		return totPage;
	}



	public void setTotPage(int totPage) {
		this.totPage = totPage;
	}



	public int getTotBlock() {
		return totBlock;
	}



	public void setTotBlock(int totBlock) {
		this.totBlock = totBlock;
	}



	public int getNowPage() {
		return nowPage;
	}



	public void setNowPage(int nowPage) {
		this.nowPage = nowPage;
	}



	public int getNowBlock() {
		return nowBlock;
	}



	public void setNowBlock(int nowBlock) {
		this.nowBlock = nowBlock;
	}



	public int getStartNo() {
		return startNo;
	}



	public void setStartNo(int startNo) {
		this.startNo = startNo;
	}



	public int getEndNo() {
		return endNo;
	}



	public void setEndNo(int endNo) {
		this.endNo = endNo;
	}



	public int getStartPage() {
		return startPage;
	}



	public void setStartPage(int startPage) {
		this.startPage = startPage;
	}



	public int getEndPage() {
		return endPage;
	}



	public void setEndPage(int endPage) {
		this.endPage = endPage;
	}
	
	// 페이지 분리 관련된 필드의 get / set
	
	
}
